package com.xiaodu.jpa;

import com.querydsl.jpa.impl.JPAQueryFactory;
import com.xiaodu.jpa.entity.ProductEntity;
import com.xiaodu.jpa.entity.QProductEntity;
import com.xiaodu.jpa.repository.OrderRepository;
import com.xiaodu.jpa.repository.ProductRepository;
import com.xiaodu.spring.base.SpringBeanUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.data.domain.*;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

import javax.persistence.EntityManager;
import javax.persistence.criteria.Path;
import javax.persistence.criteria.Predicate;
import javax.sql.DataSource;
import java.util.Date;
import java.util.List;
import java.util.Optional;
import java.util.UUID;

/**
 * @author: dutianjie
 * @date: 2021/4/20
 * @version: v1.0.0
 * @description: 描述
 **/
@SpringBootApplication(scanBasePackages = {"com.xiaodu.spring.base"})
@EnableJpaRepositories(basePackages = {"com.xiaodu.jpa.repository"})
public class JpaStartApplication implements CommandLineRunner {

    @Autowired
    private ProductRepository productRepository;

    @Autowired
    private OrderRepository orderRepository;

    @Autowired
    private EntityManager entityManager;

    public static void main(String[] args) {
        SpringApplication.run(JpaStartApplication.class, args);
    }

    @Override
    public void run(String... args) throws Exception {
//
//        testCrudRepository();
//
//        testPagingAndSortingRepository();
//
//        testJpaRepository();
//
//        testMyRepository();
//
//        testSpecification();
        testQuerydsl();
    }

    private void testQuerydsl() {
        JPAQueryFactory jpaQueryFactory = new JPAQueryFactory(entityManager);
        QProductEntity productEntity = QProductEntity.productEntity;

        ProductEntity xiaomi = jpaQueryFactory.selectFrom(productEntity)
                .where(productEntity.pName.eq("xiaomi")
                        .and(productEntity.price.lt(2999)
                                .or(productEntity.price.gt(1999))))
                .fetchOne();// fetch 执行sql语句

        /*
         *     select
         *         productent0_.p_id as p_id1_2_,
         *         productent0_.p_name as p_name2_2_,
         *         productent0_.price as price3_2_,
         *         productent0_.sc_date as sc_date4_2_,
         *         productent0_1_.order_no as order_no1_0_
         *     from
         *         t_product productent0_
         *     left outer join
         *         order_product productent0_1_
         *             on productent0_.p_id=productent0_1_.p_id
         *     where
         *         productent0_.p_name=?
         *         and (
         *             productent0_.price<?
         *             or productent0_.price>?
         *         )
         **/
    }

    private void testSpecification() {

        ProductEntity p = new ProductEntity();
        p.setpName("xiaomi");
        p.setPrice(1000L);
        List<ProductEntity> all = productRepository.findAll(Specification.where((root, query, criteriaBuilder) -> {
            Predicate pName = criteriaBuilder.like(root.get("pName"), "%" + p.getpName() + "%");
            return criteriaBuilder.and(pName, criteriaBuilder.or(criteriaBuilder.lessThan(root.get("price"), 29900L),
                    criteriaBuilder.greaterThan(root.get("price"), p.getPrice())));
        }));
        /*
         *     select
         *         productent0_.p_id as p_id1_0_,
         *         productent0_.p_name as p_name2_0_,
         *         productent0_.price as price3_0_,
         *         productent0_.sc_date as sc_date4_0_
         *     from
         *         t_product productent0_
         *     where
         *         (
         *             productent0_.p_name like '%xiaomi%'
         *         )
         *         and (
         *             productent0_.price<29900
         *             or productent0_.price>1000
         *         )
         */

        // 分页查询

        productRepository.findAll((root, query, cb) -> {
            Path<String> pName = root.get("pName");
            Path<Long> price = root.get("price");
            return cb.and(cb.or(cb.greaterThan(price, 199900L), cb.lessThan(price, 399900L)), cb.like(pName, "%xiaomi%"));
        }, PageRequest.of(1, 5));
        /*
         *  select
         *         productent0_.p_id as p_id1_0_,
         *         productent0_.p_name as p_name2_0_,
         *         productent0_.price as price3_0_,
         *         productent0_.sc_date as sc_date4_0_
         *     from
         *         t_product productent0_
         *     where
         *         (
         *             productent0_.price>199900
         *             or productent0_.price<399900
         *         )
         *         and (
         *             productent0_.p_name like ?
         *         ) limit ?, ?
         */
    }

    private void testMyRepository() {

        List<ProductEntity> xiaomi = productRepository.findAllByPNameLikeAndPriceGreaterThanEqual("%xiaomi%", 20000L);

        List<ProductEntity> productRepositories = productRepository.selectAll();

        // test update 语句
        ProductEntity p = new ProductEntity();
        p.setpId("86a51362b7944125b3e229f3e7a6c3d8");
        p.setpName("123123");
        int i = productRepository.updateProductNameById(p);

        // test page
        Page<ProductEntity> productRepositories1 = productRepository.pageByPrice(299900L, PageRequest.of(1, 15));
    }

    private void testJpaRepository() {
        //test QueryByExampleExecutor; 使用Example 构建条件
        //Example 只能对 string 类型进行匹配查询，并且是有限的； 使用上了解即可
        ProductEntity p = new ProductEntity();
        p.setPrice(199900L);
        p.setpName("xiaoMi");
        // where price = ? and p_name = ?
        List<ProductEntity> productEntities = productRepository.findAll(Example.of(p));

        // 使用ExampleMatcher  构建like;  where price = 199900 or  p_name like ? escape ?
        ExampleMatcher exampleMatcher = ExampleMatcher.matchingAny()
                .withMatcher("pName", ExampleMatcher.GenericPropertyMatchers.contains());
        List<ProductEntity> productList = productRepository.findAll(Example.of(p, exampleMatcher));

    }

    private void testCrudRepository() {
        ProductEntity p = new ProductEntity();
        p.setPrice(199900L);
        p.setpName("xiaomi 11");
        p.setScDate(new Date());
        String id = UUID.randomUUID().toString().replaceAll("-", "");
        p.setpId(id);
        // 添加
        productRepository.save(p);
        // 查询
        Optional<ProductEntity> byId = productRepository.findById(id);
        // count
        long count = productRepository.count();
        // 修改
        p.setPrice(299900L);
        productRepository.save(p);
        // 删除
//        productRepository.deleteById(id);


    }

    private void testPagingAndSortingRepository() {
        // 排序
        productRepository.findAll(Sort.by(Sort.Order.desc("scDate")));

        // 分页加排序
        productRepository.findAll(PageRequest.of(1, 5, Sort.by(Sort.Order.desc("scDate"))));

    }
}
